/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Bussiness;

import Connection.Location;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import Connection.DBUtil;
import java.sql.PreparedStatement;

/**
 *
 * @author SONY
 */
public class LocationDAO {
    private static Connection conn = null;
    private static CallableStatement cs = null;
    private static ResultSet rs = null;
    private static Vector locations;
    private static DBUtil objDBUtil = new DBUtil();
    private static Location objLocation = new Location();
    
    public static Vector getComboBoxLocationFromDB() throws SQLException {
        conn = objDBUtil.connect();
        cs = conn.prepareCall("{call spLocation}");
        rs = cs.executeQuery();
        locations = new Vector();
        locations.add("Select");
        while (rs.next()) {
            locations.addElement(rs.getString(3));
        }
        return locations;
    }

    public static Vector getComboBoxLocationIDFromDB() throws SQLException {
        conn = objDBUtil.connect();
        cs = conn.prepareCall("{call spLocation}");
        rs = cs.executeQuery();
        locations = new Vector();
        locations.add("Select");
        while (rs.next()) {
            locations.addElement(rs.getString(2));
        }
        return locations;
    }
    
    public static Vector getComboBoxLocationIDFromDBSelect() throws SQLException {
        conn = objDBUtil.connect();
        cs = conn.prepareCall("{call spLocation}");
        rs = cs.executeQuery();
        locations = new Vector();
        locations.add("Select Location");
        while (rs.next()) {
            locations.addElement(rs.getString(3));
        }
        return locations;
    }
    
    public static void insertLocationToDB(String locationID, String locationName, int locationAllowance) throws SQLException{
        String SQL;
        DBUtil objDBUtil = new DBUtil();
        Connection conn = objDBUtil.connect();
        SQL = "INSERT INTO Location VALUES (?, ?, ?)";
            PreparedStatement pstmt = conn.prepareStatement(SQL);
            pstmt.setString(1, locationID);
            pstmt.setString(2, locationName);
            pstmt.setInt(3, locationAllowance);
            pstmt.executeUpdate();
            conn.close();
    }
    
    public static void upDatetoDB(String locationID, String locationName, String locationAllowance) throws SQLException{
        String SQL;
        DBUtil objDBUtil = new DBUtil();
        Connection conn = objDBUtil.connect();    
        SQL = "UPDATE Location SET locationID=?, locationName=?, locationAllowance=?" + " WHERE locationID=?";
            PreparedStatement pstmt = conn.prepareStatement(SQL);
            pstmt.setString(4, locationID);
            pstmt.setString(1, locationID);
            pstmt.setString(2, locationName);
            pstmt.setString(3, locationAllowance);
                //buoc 5: thuc thi cau lenh sql
            pstmt.executeUpdate();
            conn.close();
    }
    
    public static void deleteToDB(String LocationID) throws SQLException{
        String SQL;
        DBUtil objDBUtil = new DBUtil();
        Connection conn = objDBUtil.connect(); 
        SQL = "DELETE FROM Location WHERE LocationID=?";
        PreparedStatement pstmt = conn.prepareStatement(SQL);
        //String DepartmentID = tbBang.getValueAt(tbBang.getSelectedRow(), 1).toString();
        pstmt.setString(1, LocationID);
        pstmt.executeUpdate();
        conn.close();
    }
    
    public static String getLocationID(String LocationName) throws SQLException{
        conn = objDBUtil.connect();
        cs = conn.prepareCall("{call spGetLocationID(?)}");
        cs.setString(1, LocationName);
        rs = cs.executeQuery();
        String LocationID = null;
        while(rs.next()){
            LocationID = rs.getString(1);
        }
        return LocationID;
    }
    
    public static String getLocationName(String LocationID) throws SQLException{
        conn = objDBUtil.connect();
        cs = conn.prepareCall("{call spGetLocationName(?)}");
        cs.setString(1, LocationID);
        rs = cs.executeQuery();
        String LocationName = null;
        while(rs.next()){
            LocationName = rs.getString(1);
        }
        return LocationName;
    }
    
    public static Location searchLocationByID(String LocationID) throws SQLException{
        conn = objDBUtil.connect();
        cs = conn.prepareCall("{call spSearchLocationByID(?)}");
        cs.setString(1, LocationID);
        rs = cs.executeQuery();
        while (rs.next()) {            
            objLocation.setStt(rs.getInt(1));
            objLocation.setLocationID(rs.getString(2));
            objLocation.setLocationName(rs.getString(3));
            objLocation.setLocationAllowance(rs.getInt(4));
        }
        return objLocation;
    }
    
    public static int searchLocationByName(String LocationName) throws SQLException{
        conn = objDBUtil.connect();
        cs = conn.prepareCall("{call spSearchLocationByName(?)}");
        cs.setString(1, LocationName);
        rs = cs.executeQuery();
        int count = 0;
        while (rs.next()) {            
            count++;
        }
        return count;
    }
    
    public static int countSearchLocationID(String locationID) throws SQLException{
        conn = objDBUtil.connect();
        cs = conn.prepareCall("{call spSearchLocationByID(?)}");
        cs.setString(1, locationID);
        rs = cs.executeQuery();
        int count = 0;
        while (rs.next()){
            count++;
        }
        return count;
    }
    
    public static int countTotalLocation() throws SQLException{
        conn = objDBUtil.connect();
        cs = conn.prepareCall("{call spLocation}");
        rs = cs.executeQuery();
        int count = 0;
        while (rs.next()) {            
            count++;
        }
        return count;
    }
    
    public static int getAllowance(String LocationID) throws SQLException{
        conn = objDBUtil.connect();
        cs = conn.prepareCall("{call spGetAllowanceLocation(?)}");
        cs.setString(1, LocationID);
        rs = cs.executeQuery();
        int allowance = 0;
        while (rs.next()) {            
            allowance = rs.getInt(1);
        }
        return allowance;
    }
}
